﻿using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;

namespace ExcelSLN.Controllers
{
    public static class MExcel
    {
        public static string Export<T>(List<T> datas, string sheetName) where T : class
        {
            string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "exports", DateTime.Now.ToString(("yyyyMMddhhmmssfff")) + ".xlsx");
            FileInfo fileInfo = new FileInfo(path);
            ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
            using (ExcelPackage pck = new ExcelPackage(fileInfo))
            {
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName);
                ws.Cells["A1"].LoadFromCollection(datas, true);
                pck.Save();
            }
            return path;
        }
        /// <summary>
        /// 解析返回指定的excel数据
        /// </summary>
        /// <param name="excelAddress"></param>
        /// <returns></returns>
        public static DataTable Resolve(string excelAddress)
        {
            ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
            using (ExcelPackage package = new ExcelPackage(excelAddress))
            {
                int vSheetCount = package.Workbook.Worksheets.Count; //获取总Sheet页

                ExcelWorksheet worksheet = package.Workbook.Worksheets[0];//选定 指定页

                int maxColumnNum = worksheet.Dimension.End.Column;//最大列
                int minColumnNum = worksheet.Dimension.Start.Column;//最小列

                int maxRowNum = worksheet.Dimension.End.Row;//最小行
                int minRowNum = worksheet.Dimension.Start.Row;//最大行

                DataTable vTable = new DataTable();
                DataColumn vC;
                for (int j = 1; j <= maxColumnNum; j++)
                {
                    vC = new DataColumn(worksheet.Cells[1, j].Value.ToString(), typeof(string));
                    vTable.Columns.Add(vC);
                }
                //if (maxRowNum > 200)
                //{
                //    maxRowNum = 200;
                //}
                for (int n = 1; n <= maxRowNum; n++)
                {
                    DataRow vRow = vTable.NewRow();
                    for (int m = 1; m <= maxColumnNum; m++)
                    {
                        vRow[m - 1] = worksheet.Cells[n, m].Value;
                    }
                    vTable.Rows.Add(vRow);
                }
                return vTable;

            }
        }

        /// <summary>
        /// 解析返回指定的excel数据
        /// </summary>
        /// <param name="excelAddress"></param>
        /// <returns></returns>
        public static DataTable Resolve(Stream file)
        {
            ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
            using (ExcelPackage package = new ExcelPackage(file))
            {
                int vSheetCount = package.Workbook.Worksheets.Count; //获取总Sheet页

                ExcelWorksheet worksheet = package.Workbook.Worksheets[0];//选定 指定页

                int maxColumnNum = worksheet.Dimension.End.Column;//最大列
                int minColumnNum = worksheet.Dimension.Start.Column;//最小列

                int maxRowNum = worksheet.Dimension.End.Row;//最小行
                int minRowNum = worksheet.Dimension.Start.Row;//最大行

                DataTable vTable = new DataTable();
                DataColumn vC;
                for (int j = 1; j <= maxColumnNum; j++)
                {
                    vC = new DataColumn(worksheet.Cells[1, j]?.Value?.ToString()??"", typeof(string));
                    vTable.Columns.Add(vC);
                }
                //if (maxRowNum > 1000)
                //{
                //    maxRowNum = 1000;
                //}
                for (int n = 1; n <= maxRowNum; n++)
                {
                    DataRow vRow = vTable.NewRow();
                    for (int m = 1; m <= maxColumnNum; m++)
                    {
                        vRow[m - 1] = worksheet.Cells[n, m].Value;
                    }
                    vTable.Rows.Add(vRow);
                }
                return vTable;

            }
        }
    }
}
